﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace BudjetProjekat
{
    public partial class Form1 : Form
    {
        //KalendarKolona datum;
        bool dugmeNovaGrupa = false;
        bool dugmeNovaKategorija = false;
        SqlConnection konekcija;
        int brRedova;
        int noviBrRedova;

        SqlCommand sCommand;
        SqlDataAdapter sAdapter;
        SqlCommandBuilder sBuilder;
        DataSet sDs;
        DataTable sTable;

      

        int FK_ID_Kategorija_index;

        public Form1()
        {
            InitializeComponent();
            new_btn.Enabled=false;
            save_btn.Enabled = false;
            delete_btn.Enabled=false;
            btnBrisi.Enabled = false;
            //datum = new KalendarKolona();
            //dataGridView1.Columns.Add(datum);
            //dataGridView1.Columns[3].HeaderText = "Datum";
            //KONEKCIJA NA BAZU------------- 
            konekcija = new SqlConnection();
            konekcija.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=BudjetDB;Integrated Security=True;MultipleActiveResultSets=True";
            konekcija.Open();
            //------------------------------
            //Popunjavanje treeview-a grupama iz baze
            SqlDataAdapter da = new SqlDataAdapter("select naziv from grupa", konekcija);
            DataSet ds1 = new DataSet();
            da.Fill(ds1);
            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                treeView1.Nodes.Add(ds1.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
            //----------------------------------------

            for (int j = 0; j < treeView1.Nodes.Count; j++)
            {
                if (treeView1.Nodes[0].Level == 0)
                {
                    SqlDataAdapter da1 = new SqlDataAdapter("select kategorija.Naziv from kategorija,grupa where kategorija.FK_ID_Grupa = Grupa.ID and grupa.Naziv = \'" + treeView1.Nodes[j].Text +"\'", konekcija);
                    DataSet ds2 = new DataSet();
                    da1.Fill(ds2);
                    for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
                        treeView1.Nodes[j].Nodes.Add(ds2.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());

                } 
            }
                    pNovaGrupa.Hide();
                    pKategorija.Hide();
                    btnNovaKategorija.Enabled = false;
                    RefreshBojuTabele();
          
        }
        private void selektCvora(String naziv)
        {
           /* if(naziv.Equals("Struja")){
                string[] row = new string[] { "Odjeća i obuća", "500 KM", "250 KM","20.11.2002" };
                dataGridView1.Rows.Add(row);
                row = new string[] { "Automobil", "1000 KM" };
                dataGridView1.Rows.Add(row);
                row = new string[] { "Zdravstvo", "250 KM" };
                dataGridView1.Rows.Add(row);
                row = new string[] { "Higijena", "250 KM" };
                dataGridView1.Rows.Add(row);
                RefreshBojuTabele();
            }*/

            int level = treeView1.SelectedNode.Level;
            if (level == 0)
            {
                SqlCommand cmd = new SqlCommand("sp_Transakcija_SELECT_PoGrupi", konekcija);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@grupa", treeView1.SelectedNode.Text));
                SqlDataReader reader = cmd.ExecuteReader();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                ds.Tables.Add(dt);
                ds.Load(reader, LoadOption.PreserveChanges, ds.Tables[0]);
                dataGridView1.DataSource = ds.Tables[0];
                dataGridView1.ReadOnly = true;
            }
            else{
                
                dataGridView1.ReadOnly = false;
                /*SqlCommand cmd = new SqlCommand("sp_Transakcija_SELECT_PoKategoriji", konekcija);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@kategorija", treeView1.SelectedNode.Text));
                SqlDataReader reader = cmd.ExecuteReader();
                //SqlDataAdapter a = new SqlDataAdapter();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                ds.Tables.Add(dt);
                ds.Load(reader, LoadOption.PreserveChanges, ds.Tables[0]);
                dataGridView1.DataSource = ds.Tables[0];

                brRedova = dataGridView1.RowCount-1;
                Console.WriteLine("stari: "+brRedova);
                //konekcija.Close(); */

                string sql = "select * from transakcija where FK_ID_Kategorija = (select ID from Kategorija where Naziv = \'" + treeView1.SelectedNode.Text + "\')";
                
                sCommand = new SqlCommand(sql, konekcija);
                //sCommand.CommandType = CommandType.StoredProcedure;
                //sCommand.Parameters.Add(new SqlParameter("@kategorija", treeView1.SelectedNode.Text));
                sAdapter = new SqlDataAdapter(sCommand);
                sBuilder = new SqlCommandBuilder(sAdapter);
                sDs = new DataSet();
                sAdapter.Fill(sDs,"Transakcija");
                sTable = sDs.Tables["Transakcija"];
                //konekcija.Close();
                dataGridView1.DataSource = sDs.Tables["Transakcija"];
                dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[6].Visible = false;
                dataGridView1.Columns[4].Visible = false;
                dataGridView1.Columns[5].Visible = false;
                dataGridView1.Columns[7].Visible = false;
                dataGridView1.ReadOnly = true;
                save_btn.Enabled = false;
                dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                
                
               /* string connectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=BudjetDB;Integrated Security=True";
                string sql = "SELECT * FROM Grupa";
                SqlConnection connection = new SqlConnection(connectionString);
                try
                {
                    connection.Open();
                    Console.WriteLine("OK");
                }
                catch (Exception)
                {
                }
                sCommand = new SqlCommand(sql, connection);
                sAdapter = new SqlDataAdapter(sCommand);
                sBuilder = new SqlCommandBuilder(sAdapter);
                sDs = new DataSet();
                sAdapter.Fill(sDs, "Grupa");
                sTable = sDs.Tables["Grupa"];
                connection.Close();
                dataGridView1.DataSource = sDs.Tables["Grupa"];
                //dataGridView1.ReadOnly = true;
                
                dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                */
            }

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            btnBrisi.Enabled=true;
            selektCvora(treeView1.SelectedNode.Text);
            //test da li je cvor root
            int level = treeView1.SelectedNode.Level;
            if (level == 0)
            {
                btnNovaKategorija.Enabled = true;
                new_btn.Enabled = false;
            }
            else
            {
                btnNovaKategorija.Enabled = false;
                new_btn.Enabled = true;

            }
            //Računanje FK_ID_Kategorija za zadnju funkciju
            string sql1 = "select ID from Kategorija where Naziv = \'" + treeView1.SelectedNode.Text + "\'";
            //string sql2 = select FK_ID_TIp from transakcija where FK_ID_TIp = (select ID from Tip_Transakcije where Naziv = 'Pravi');
            //string sql3 = "select FK_ID_Kategorija from transakcija where FK_ID_Kategorija = (select ID from Kategorija where Naziv = \'" + treeView1.SelectedNode.Text + "\')";
            sCommand = new SqlCommand(sql1, konekcija);
            //-------
            FK_ID_Kategorija_index = Convert.ToInt32(sCommand.ExecuteScalar());
            Console.WriteLine(sCommand.ExecuteScalar());
/*
            if (sTable != null && sTable.Rows.Count - 1>0)
                if (sTable.Rows[sTable.Rows.Count-1][0].ToString() != "")
                    sAdapter.Update(sTable);*/
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (!dugmeNovaGrupa)
            {
                pNovaGrupa.Show();
                dugmeNovaGrupa = true;
            }
            else
            {
                dugmeNovaGrupa = false;
                pNovaGrupa.Hide();
            }
            txtNaslovGrupe.Clear();
            pKategorija.Hide();
            dugmeNovaKategorija = false;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            String naslov = txtNaslovGrupe.Text;
            if (!naslov.Equals(""))
            {
                TreeNode newNode = new TreeNode(naslov);
                // treeView1.SelectedNode.Nodes.Add(newNode);
                treeView1.Nodes.Add(newNode);
                pNovaGrupa.Hide();
                dugmeNovaGrupa = false;
            }
            
            //BAZA
            SqlCommand cmdIns = new SqlCommand("sp_Grupa_INSERT", konekcija);
            cmdIns.CommandType = CommandType.StoredProcedure;
            cmdIns.Parameters.Add(new SqlParameter("@naziv", txtNaslovGrupe.Text));
            cmdIns.ExecuteReader();

            txtNaslovGrupe.Clear();
        }

        private void btnNovaKategorija_Click(object sender, EventArgs e)
        {
            if (!dugmeNovaKategorija)
            {
                pKategorija.Show();
                dugmeNovaKategorija = true;
            }
            else
            {
                dugmeNovaKategorija = false;
                pKategorija.Hide();
            }
            txtNovaKategorija.Clear();
            pNovaGrupa.Hide();
            dugmeNovaGrupa = false;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            String naslov = txtNovaKategorija.Text;
            if (!naslov.Equals(""))
            {
                TreeNode newNode = new TreeNode(naslov);
                treeView1.SelectedNode.Nodes.Add(newNode);
                //treeView1.Nodes.Add(newNode);
                pKategorija.Hide();
                dugmeNovaKategorija = false;
                treeView1.SelectedNode.Expand();
            }

            //BAZA
            String s = "INSERT INTO Kategorija (Naziv, FK_ID_Grupa) select" + "\'" + txtNovaKategorija.Text + "\'" + ", Grupa.ID from Grupa where Grupa.Naziv =" + "\'" + treeView1.SelectedNode.Text + "\'";

            sCommand = new SqlCommand(s, konekcija);
            sCommand.ExecuteScalar();
            
            // SqlCommand cmdIns = new SqlCommand("sp_Kategorija_INSERT", konekcija);
            /*cmdIns.CommandType = CommandType.StoredProcedure;
            cmdIns.Parameters.Add(new SqlParameter("@naziv", txtNovaKategorija.Text));
            cmdIns.Parameters.Add(new SqlParameter("@grupa", treeView1.SelectedNode.Text));*/

            //cmdIns.ExecuteReader();

            txtNovaKategorija.Clear();
        }

        private void RefreshBojuTabele()
        {
            int brRedova = dataGridView1.Rows.Count;
            DataGridViewRow theRow;
            for (int i = 1; i < brRedova; i++)
            {
                theRow = dataGridView1.Rows[i];
                if (i % 2 != 0)
                    theRow.DefaultCellStyle.BackColor = Color.Beige;
            }
  
        }

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            RefreshBojuTabele();
        }

        private void Form1_Leave(object sender, EventArgs e)
        {
            
            konekcija.Close();
        }

     
        private void button5_Click(object sender, EventArgs e)
        {
            
            dataGridView1.ReadOnly = false;
            save_btn.Enabled = true;
            new_btn.Enabled = false;
            delete_btn.Enabled = false;
        
        }

        private void delete_btn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Da li zaista želite izbrisati red!!!", "Brisanje", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                sAdapter.Update(sTable);
            }
        }

        private void save_btn_Click(object sender, EventArgs e)
        {

            int index;
            if (sTable != null)
                index = sTable.Rows.Count - 1;
            else
                index = -1;

            if (sTable != null && index != -1)
            {
                sTable.Rows[index]["FK_ID_Korisnik"] = 5; //Fiksno jer nemamo te komponente u sistemu još uvijek!!!


                sTable.Rows[index]["FK_ID_Kategorija"] = FK_ID_Kategorija_index;
                sTable.Rows[index]["FK_ID_TIp"] = 1; //Nek bude fiksno
                sTable.Rows[index]["FK_ID_Vrsta"] = 1; //Nek bude fiksno

                sAdapter.Update(sTable);
            }
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            new_btn.Enabled = true;
            delete_btn.Enabled = true;
        }

        private void btnBrisi_Click(object sender, EventArgs e)
        {

            int level = treeView1.SelectedNode.Level;
            if (level == 0)
            {
                string sql0 = "delete from Transakcija where FK_ID_Kategorija in (select id from Kategorija where FK_ID_Grupa = (select ID from Grupa where Naziv = \'" + treeView1.SelectedNode.Text + "\'))"; 
                string sql1 = "delete from Grupa where Naziv =  \'" + treeView1.SelectedNode.Text + "\'";
                string sql = "delete from Kategorija where FK_ID_Grupa = (select ID from Grupa where Naziv = \'" + treeView1.SelectedNode.Text + "\')";
                sCommand = new SqlCommand(sql0, konekcija);
                sCommand.ExecuteScalar();
                sCommand = new SqlCommand(sql, konekcija);
                sCommand.ExecuteScalar();
                sCommand = new SqlCommand(sql1, konekcija);
                sCommand.ExecuteScalar();
                treeView1.SelectedNode.Remove();
            }
            else
            {
                //Brisanje kategorije
                string sql1 = "delete from Transakcija where FK_ID_Kategorija = (select id from Kategorija where Naziv = \'" + treeView1.SelectedNode.Text + "\')" ;
                string sql = "delete from Kategorija where Naziv = \'" + treeView1.SelectedNode.Text + "\'";
                sCommand = new SqlCommand(sql1, konekcija);
                sCommand.ExecuteScalar();
                sCommand = new SqlCommand(sql, konekcija);
                sCommand.ExecuteScalar();
                treeView1.SelectedNode.Remove();
                dataGridView1.Refresh();
            }

            if (treeView1.Nodes.Count == 0)
            {
                btnBrisi.Enabled = false;
                dataGridView1.DataSource = null;
            }
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            Form2 v = new Form2();
            v.Show();
        }
    }
}

